Snowflakeのユーザーステージ経由でファイルをロードしてみた

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

Snowflakeには「ステージ」というSnowflakeのテーブルにファイルからデータをロードする際に、そのファイルを置く場所があります。

「ステージ」の種類としては大きく２つあり、Snowflake内部にある「内部ステージ」と、各種クラウド上（Amazon S3, Google Cloud Storage, Microsoft Azure）に存在するファイルとの架け橋になる「外部ステージ」があります。

今回はこのうちの「内部ステージ」における「ユーザーステージ」を利用して、ファイルデータをテーブルへロードしてみました。

内部ステージについての概要は以下の記事でもまとめています。

「やりたいこと」と「事前準備」

ローカルPCにあるCSVファイルを、「ユーザーステージ」を経由してSnowflake上のテーブルにロードしてみたいと思います。

今回利用するファイルは以下のようなCSVファイルとします。

id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

Snowflakeには事前にデータベースとテーブルを用意しておきます。

USE ROLE OOTAKA_SANDBOX_ROLE; -- ROLEは事前作成済みのものを利用します
CREATE DATABASE OOTAKA_SANDBOX_DB;

USE DATABASE OOTAKA_SANDBOX_DB;
CREATE TABLE public.users(
  id INTEGER,
  name STRING,
  age INTEGER
);

これらを利用して、進めていきます。

「ユーザーステージ」のおさらい

ユーザーステージは「ユーザー」に紐づくステージで、各ユーザーにデフォルトで割り当てられています。ユーザーに紐づくのでファイルへのアクセスは自分のみとなり、ファイルは各テーブルにCOPYすることができます。 また、対象テーブルへのINSERT権限が必要となります。

これを踏まえると、ステージは既にできており、テーブルも先程作成したテーブルにINSERT権限がある状態なので問題ないですね。

ファイルのステージング

では、準備ができたのでまずはファイルをステージング（アップロード）します。ファイルのステージングは下記を参考に進めます。

ステージングは以下のようにPUTコマンドを使って、ローカルファイルをユーザーステージ上の指定の場所にステージングできるようです。

put file:///data/data.csv @~/staged;

今回はSnowSQLを使ってPUTしてみます。まずはステージングするファイルの確認から。

$ cat /tmp/users.csv
id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

問題ないですね。SnowSQLを起動してこれをステージングしてみます。ファイルはユーザーステージのusersディレクトリ配下にステージングします。

$ snowsql
* SnowSQL * v1.2.7
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>put file:///tmp/users.csv @~/users;
users.csv_c.gz(0.00MB): [##########] 100.00% Done (0.097s, 0.00MB/s).
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source    | target       | source_size | target_size | source_compression | target_compression | status   | message |
|-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------|
| users.csv | users.csv.gz |         130 |         147 | NONE               | GZIP               | UPLOADED |         |
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 1.240s

ステージングされました！デフォルトでGZIP圧縮も行ってくれているのが分かります。念のため、LISTコマンドでユーザーステージを確認してみます。

foo_bar#(no warehouse)@(no database).(no schema)>list @~/users;
+--------------------+------+----------------------------------+------------------------------+
| name               | size | md5                              | last_modified                |
|--------------------+------+----------------------------------+------------------------------|
| users/users.csv.gz |  160 | 0a75b2ffe08fad9f527f90f4729633ec | Thu, 9 Jul 2020 01:16:06 GMT |
+--------------------+------+----------------------------------+------------------------------+
1 Row(s) produced. Time Elapsed: 0.147s

ちゃんとusers配下にusers.csv.gzファイルがありますね。

データのコピー

ステージングが出来たので、ファイルからテーブルへデータをコピーしてみます。コピーについてはこちらを参考に実施します。

コピーは以下のようにCOPYコマンドを使って、ユーザーステージ上のファイルを、指定したテーブルとファイルフォーマットでコピーできるようです。

copy into mytable from @~/staged file_format = (format_name = 'my_csv_format');

ではやってみます。まずは、ウェアハウス、データベースを指定しておきます。

foo_bar#(no warehouse)@(no database).(no schema)>USE WAREHOUSE X_SMALL_WH;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.591s
foo_bar#X_SMALL_WH@(no database).(no schema)>USE DATABASE OOTAKA_SANDBOX_DB;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.134s

テーブルの中身が空なのも確認しておきましょう。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>SELECT * FROM USERS;
+----+------+-----+
| ID | NAME | AGE |
|----+------+-----|
+----+------+-----+
0 Row(s) produced. Time Elapsed: 0.168s

空ですね。ではCOPYコマンドを使ってコピーしてみます。今回、ファイルフォーマットのオブジェクトは作成していないので、format_nameで指定はせずに、明示的に指定してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @~/users file_format = (type = csv field_delimiter = ',' skip_header = 1);
+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file               | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| users/users.csv.gz | LOADED |           9 |           9 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+--------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 1.171s

成功しました！テーブルを確認してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>SELECT * FROM USERS;
+----+-------------+-----+
| ID | NAME        | AGE |
|----+-------------+-----|
|  1 | Aruto Hiden |  22 |
|  2 | Izu         | NULL |
|  3 | Isamu Fuwa  |  27 |
|  4 | Yua Yaiba   |  24 |
|  5 | Gai Amatsu  |  45 |
|  6 | Horobi      | NULL |
|  7 | Naki        | NULL |
|  8 | Jin         | NULL |
|  9 | Ikazuchi    | NULL |
+----+-------------+-----+
9 Row(s) produced. Time Elapsed: 0.280s

想定通りコピーされました！

ステージングしたファイルの削除

最後に、ステージングしたファイルをREMOVEコマンドで削除しておきます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>remove @~/users;
+--------------------+---------+
| name               | result  |
|--------------------+---------|
| users/users.csv.gz | removed |
+--------------------+---------+
1 Row(s) produced. Time Elapsed: 0.644s

削除できました。一応確認してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>list @~/users;
+------+------+-----+---------------+
| name | size | md5 | last_modified |
|------+------+-----+---------------|
+------+------+-----+---------------+
0 Row(s) produced. Time Elapsed: 0.562s

問題なく消えていますね！

まとめ

以上、ユーザーステージ経由でファイルをロードしてみました。ユーザーステージであれば、デフォルトで自分用に用意されているのでお手軽に利用できそうですね。

どなたかのお役に立てば幸いです。それでは！